<%@ page language="java" import="org.json.*,java.sql.*,java.util.*,com.microsoft.sqlserver.jdbc.*,java.io.*,web.rec.*" 
contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"  %>

<% 
	Connection conn = ResourceTool.getConnection();
%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
<script type="text/javascript" src="script/jquery-1.6.3.min.js"></script>
<script type="text/javascript" src="script/form_script.js"></script>
</head>
<body>
          <%
			try {
			// Create the prepared statement and use it to
			// INSERT the student attrs INTO the Student table.
			Statement stmt = conn.createStatement();
			String action = request.getParameter("action");
			String fall = "fall";
			%>
			<table>
				<tr>
				<th>Concentration</th>
				<th>Required Units</th>
				<th>Min GPA</th>
				<th>Units taken</th>
				<th>GPA of taken courses</th>
				</tr>
			<%
			String student_id = request.getParameter("student_id");
			String degree_name = request.getParameter("degree_name");
			if (action != null && action.equals("submit"))
			{
				//all the concentration names
				String sql = "select * from Concentration where "
						   + "Concentration.degree_name =  '" + degree_name + "'";
				ResultSet rs = stmt.executeQuery(sql);
				Map<String,Integer> concentrationUnits = new HashMap<String, Integer>();
				Map<String,Float> concentrationMinGPA = new HashMap<String, Float>();
				Map<String, Set<String>> concentrationCourses =  new HashMap<String, Set<String>>();
				Map<String,Integer> concentrationUnitsTaken = new HashMap<String, Integer>();
				Map<String,Float> concentrationGPATaken = new HashMap<String, Float>();
//				Map<String, Set<String>> concentrationCoursesTaken =  new HashMap<String, Set<String>>();
				Set<String> courses = null;
				while(rs.next())
				{
					concentrationUnits.put(rs.getString("name"), rs.getInt("min_units"));
					concentrationMinGPA.put(rs.getString("name"), rs.getFloat("min_gpa"));
				}
				//list of courses belongs to a specific concentration
				for(String concentration: concentrationUnits.keySet())
				{
					
					courses = new HashSet<String>();
					sql = "select * from ConcentrationCourses where "
							   + "concentration_name =  '" + concentration + "'";
					rs = stmt.executeQuery(sql);
					while(rs.next())
					{
						courses.add(rs.getString("course_num"));
					}
					concentrationCourses.put(concentration, courses);
				}
				int takenUnits;
				int gpaUnits;
				float totalGrade;
				String letterGrade;
				float grade;
				int units;
				float gpa;
				for(String concentration: concentrationUnits.keySet())
				{
					takenUnits = 0;
					gpaUnits = 0;
					totalGrade = 0;
					grade = 0;
					units = 0;
					gpa = 0;
					Set<String> coursesTaken = new HashSet<String>();
					sql = "select * from Student, StudentTakingSection, Section, Class, Course,ConcentrationCourses where "
				   	       + "Student.student_id = StudentTakingSection.student_id and "
						   + "Section.section_id = StudentTakingSection.section_id and "
						   + "Section.class_id = Class.class_id and "
						   + "Class.course_num = Course.course_num and "
						   + "Course.course_num = ConcentrationCourses.course_num and "
						   + "Student.student_id = '"+ student_id + "' and "
						   + "ConcentrationCourses.concentration_name = '" + concentration + "'";
					System.out.println(sql);
					rs = stmt.executeQuery(sql);
					while(rs.next())
					{
						letterGrade = rs.getString("grade");					
						units = rs.getInt("units");
						//letter or s/u, but not incomplete
						if(!letterGrade.toLowerCase().equals("in"))
						{
							takenUnits += units;
							coursesTaken.add(rs.getString("course_num"));
							if(!letterGrade.toLowerCase().equals("s")&&!letterGrade.toLowerCase().equals("u"))
							{
								grade = Float.parseFloat(letterGrade);
								gpaUnits += units;
								totalGrade += grade * units;
							}
						}
					}
					concentrationUnitsTaken.put(concentration, takenUnits);
					if(gpaUnits > 0)
					{
						gpa =  totalGrade/gpaUnits;
						concentrationGPATaken.put(concentration,gpa);
					}
					else
						concentrationGPATaken.put(concentration, 0.0f);
					//concentrationCoursesTaken.put(concentration, coursesTaken);
					courses = concentrationCourses.get(concentration);
					courses.removeAll(coursesTaken);
					%>
					<tr>
					<td><%= concentration %></td>
					<td><%= concentrationUnits.get(concentration) %></td>
					<td><%= concentrationMinGPA.get(concentration) %></td>
					<td><%= concentrationUnitsTaken.get(concentration) %></td>
					<td><%= concentrationGPATaken.get(concentration) %></td>
					</tr>
					<%
					Iterator<String> it = courses.iterator();
					String course_num = null;
					while(it.hasNext())
					{
						course_num = it.next();
					%>
					<tr>
					<td>Course left:</td>
					<td><%= course_num %></td>
					<%
						sql = "select distinct year,season from class, course where "
							+ "class.course_num = course.course_num and "
							+ "course.course_num = '" + course_num + "' and "
							+ "(class.year > 2012 or (class.year = 2012 and class.season != 'winter')) order by year";
						System.out.println(sql);
						rs = stmt.executeQuery(sql);
						int year = 0;
						String season = null;
						int lastYear = 0;
						Set<String> seasons = null;
						while(rs.next())
						{
							year = rs.getInt("year");
							if(year != lastYear)
							{
								if(seasons != null)
								{
									if(seasons.contains("Winter"))
									{
										season = "Winter";
										break;
									}
									else if(seasons.contains("Spring"))
									{
										season = "Spring";
										break;
									}
									else if(seasons.contains("Summer"))
									{
										season = "Summer";
										break;
									}
									else if(seasons.contains("Fall"))
									{
										season = "Fall";
										break;
									}
								}
								seasons = new HashSet<String>();
							}
							lastYear = year;
							seasons.add(rs.getString("season").toLowerCase());							
						}
						if (season == null && seasons != null)
						{
							if(seasons.contains("winter"))
							{
								season = "winter";
							}
							else if(seasons.contains("spring"))
							{
								season = "spring";
							}
							else if(seasons.contains("summer"))
							{
								season = "summer";
							}
							else if(seasons.contains("fall"))
							{
								season = "fall";
							}
						}
					%>
					<td><%= year>0?year:"" %></td>
					<td><%= season != null ? season:"" %></td>
					<%
					} 
					%>
					</tr>
				<% 
				}
				%>
				<tr>
				<td>finished concentrations: </td>
				<%
				int min_units;
				float min_gpa;
				for(String concentration: concentrationUnits.keySet())
				{
					min_units = concentrationUnits.get(concentration);
					min_gpa = concentrationMinGPA.get(concentration);
					takenUnits = concentrationUnitsTaken.get(concentration);
					gpa = concentrationGPATaken.get(concentration);
					if ((takenUnits >= min_units)&&(gpa >= min_gpa ))
					{
				
					%>
					<td><%= concentration %></td>
					
					<%
					}
				}
				%>
				</tr>
				<%
		}
	} catch (SQLException sqle) {
		out.println(sqle.getMessage());
	} catch (Exception e) {
		out.println(e.getMessage());
	}    
            %>
      </table>
</body>
</html>